package com.tom;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.Driver;

public class ProductDAO {
	public static Connection conn = null;

    public static Connection getConnection() {
            if (conn == null) {
                    try {
                            DriverManager.registerDriver(new Driver());
                            conn = DriverManager.getConnection(
"jdbc:mysql://j.snpy.org/j102u07?useUnicode=true&characterEncoding=UTF-8", "j102u07", "j102u07.989");

                    } catch (SQLException e) {
                            // TODO Auto-generated catch block
                            e.printStackTrace();
                    }
            }
            return conn;

    }

    public List<Product> getAll() {
            List<Product> products = new ArrayList<Product>();
            try {
                    Statement stmt = getConnection().createStatement();
                    ResultSet rs = stmt.executeQuery("select * from product");
                    while (rs.next()) {
                            String p_number = rs.getString("p_number");
                            String p_desc = rs.getString("p_desc");
                            String p_name = rs.getString("p_name");
                            String p_class = rs.getString("p_class");
                            int price= rs.getInt("price");
                            int selling = rs.getInt("selling");
                            int stock_qty = rs.getInt("stock_qty");
                            int loan_qty = rs.getInt("loan_qty");
                            int usable = rs.getInt("usable");
                            String maping = rs.getString("maping");
                            Product product = new Product(p_number, p_desc, p_name, p_class, price, selling, stock_qty, loan_qty, usable, maping);
                            products.add(product);
                    }
            } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
            }
            return products;
    }

    public int delete(String p_number) {
            String sql = "delete from product where p_number=?";
            int rowCount = 0;
            try {
                    PreparedStatement pstmt = getConnection().prepareStatement(sql);
                    pstmt.setString(1, p_number);
                    rowCount = pstmt.executeUpdate();
            } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
            }
            return rowCount;
    }
    
    public int add(Product p){
            String sql = "insert into product(p_number, p_desc, p_name, p_class, price, selling, stock_qty, loan_qty, usable, maping) values (?,?,?,?,?,?,?,?,?,?)";
            int rowCount = 0;
            try {
                    PreparedStatement pstmt = getConnection().prepareStatement(sql);
                    
                    pstmt.setString(1,p.getP_number() );
                    pstmt.setString(2,p.getP_desc() );
                    pstmt.setString(3,p.getP_name() );
                    pstmt.setString(4,p.getP_class() );
                    pstmt.setInt(5,p.getPrice() );
                    pstmt.setInt(6,p.getSelling());
                    pstmt.setInt(7,p.getStock_qty());
                    pstmt.setInt(8,p.getLoan_qty());
                    pstmt.setInt(9,p.getUsable());
                    pstmt.setString(10,p.getMaping());
                    rowCount = pstmt.executeUpdate();
                    
                    System.out.println(rowCount);
            } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
            }
            return rowCount;
    }
    
    public int update(Product p){
            String sql = "update product set p_desc=?, p_name?, p_class=?, price=?, selling=?, stock_qty=?, loan_qty=?, usable=?, maping=?  where p_number=?";
            int rowCount = 0;
            try {
                    PreparedStatement pstmt = getConnection().prepareStatement(sql);
                    pstmt.setString(1,p.getP_desc() );
                    pstmt.setString(2,p.getP_name() );
                    pstmt.setString(3,p.getP_class() );
                    pstmt.setInt(4,p.getPrice() );
                    pstmt.setInt(5,p.getSelling());
                    pstmt.setInt(6,p.getStock_qty());
                    pstmt.setInt(7,p.getLoan_qty());
                    pstmt.setInt(8,p.getUsable());
                    pstmt.setString(9,p.getMaping());
                    pstmt.setString(10,p.getP_number() );
            } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
            }
            return rowCount;
    }

    public Product getProductById(String p_number){
            Product product = null;
            String sql = "select * from product where p_number=?";
           
            try {
                    PreparedStatement pstmt = getConnection().prepareStatement(sql);
                    pstmt.setString(1, p_number);
                    ResultSet rs = pstmt.executeQuery();
                    if (rs.next()){
                            product = new Product(rs.getString("p_number")
                                            ,rs.getString("p_desc")
                                            ,rs.getString("p_name")
                                            ,rs.getString("p_class")
                                            ,rs.getInt("price")
                                            ,rs.getInt("selling")
                            				,rs.getInt("stock_qty")
                            				,rs.getInt("loan_qty")
                            				,rs.getInt("usable")
                            				,rs.getString("maping"));
                    }
            } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
            }
            return product;
    }
    
   /* public static void main(String[] args){
          testgetAll();
            ProductDAO dao = new ProductDAO();
            System.out.println(dao.delete("aa123"));
    }

    private static void testgetAll() {
            ProductDAO dao = new ProductDAO();
            List<Product> list = dao.getAll();
            System.out.println(list.size());
    }
    
    
    
    public static void main(String[] args){
    
        ProductDAO dao = new ProductDAO();
        String add=dao.add(1,2,3,4,5,6,7,8,9,0);
        System.out.println(add);

    }
*/
    public static void main(String[] args){
    	ProductDAO dao = new ProductDAO();
        List<Product> list = dao.getAll();
        System.out.println(list.size());
    	    }
    
}
